<?php
//include('CSVtoXLS.php');
include 'PHPExcel/IOFactory.php';
//function to zip and force download the files using PHP
function zipFilesAndDownload($file_names,$archive_file_name,$file_path)
{
  $path_parts = pathinfo($archive_file_name);

  echo $archive_file_name;
  //create the object
  $zip = new ZipArchive();
  //create the file and throw the error if unsuccessful
  if ($zip->open($archive_file_name, ZIPARCHIVE::CREATE )!==TRUE) {
    exit("cannot open <$archive_file_name>\n");
  }

  //add each files of $file_name array to archive
  foreach($file_names as $files)
  {
	echo $file_path.$files;
    $zip->addFile($file_path.$files,$files);
  }
  
  $zip->close();

  //then send the headers to foce download the zip file
  header("Content-type: application/zip");
  header('Content-length: '.filesize($archive_file_name));
  header("Content-Disposition: attachment; filename=".$path_parts["basename"]);
  header("Pragma: no-cache");
  header("Expires: 0");
  ob_clean();
  flush();
  readfile("$archive_file_name");
  //header("Location: dashboard.php"); 
  exit;
}
 
function DeleteAllFiles($dir)
{
  //echo $dir;
  //die();
	foreach(glob($dir.'*.*') as $v){
       unlink($v);
   }
   //die();
}

Function ExportCsvPMCPreMediation($dateStart,$dateEnd)
{

   include('connect-db.php');
     //--- get list of mediator ---
/*	 $sql_script = 'SELECT mediator.mediatorName, casereferred.dateRegister,casereferred.CaseNo, schedule.status FROM mediator LEFT JOIN casereferred ON casereferred.mediatorId = mediator.mediatorId LEFT JOIN schedule ON schedule.caseNo = casereferred.CaseNo ';
	 $sql_script .= 'WHERE dateRegister BETWEEN "'.$dateStart.'" AND "'.$dateEnd.'"';
	 
     $result = mysql_query($sql_script)
				or die(mysql_error());
     // Put them in array
	 $array = array();
	 while($row = mysql_fetch_assoc($result)){
		if(strtolower($row['status']) != 'reset') 
		     array_push($array,$row['mediatorName']."-".$row['CaseNo']."-".$row['status']);
	//for($i = 0; $array[$i] = mysql_fetch_assoc($result); $i++) ;
	}
	print_r($array);
	
	exit;
 */  
	//------ Pre-Mediation Report Generation ------
	$sql_script = 'SELECT casereferred.dateRegister, casereferred.controlID, casereferred.CaseNo, typeofcase.Tdescription, casereferred.plaintiffName, ';
	$sql_script .= 'casereferred.defendantName, natureofcase.Ndescription, judges.courtOrigin, judges.referringJudge, schedule.Date,schedule.status, mediator.mediatorName ';
	$sql_script .= 'FROM casereferred ';
	$sql_script .= 'LEFT JOIN schedule ON casereferred.CaseNo = schedule.caseNo ';
	$sql_script .= 'LEFT JOIN natureofcase ON casereferred.natureOfCase = natureofcase.code ';
	$sql_script .= 'LEFT JOIN typeofcase ON casereferred.typeId = typeofcase.typeId ';
	$sql_script .= 'LEFT JOIN judges ON casereferred.judgeId = judges.judgeId ';
	$sql_script .= 'LEFT JOIN mediator ON casereferred.mediatorId = mediator.mediatorId ';
	$sql_script .= 'WHERE dateRegister BETWEEN "'.$dateStart.'" AND "'.$dateEnd.'"';
	
	//echo $sql_script;
	//die();

    $result = mysql_query($sql_script)
				or die(mysql_error()); 
	$special_chr = array(".",",","-",">","<","&","&amp;");
	
	$text = "Period Covered, Unit Control No., Docket No. (Case Number),Type Of Case, Plaintiff, Dependant, Nature of Case, Court, Referring Judge, Mediation Date, Status, Mediator\r";
	while($row = mysql_fetch_array($result)){
	    $text .= $row['dateRegister'].",".$row['controlID'].",".$row['CaseNo'].",".$row['Tdescription'].",".str_replace($special_chr," ",$row['plaintiffName']).",";
        $text .= str_replace($special_chr," ",$row['defendantName']).",".$row['Ndescription'].",".$row['courtOrigin'].",".$row['referringJudge'].",";
		$text .= $row['Date'].",".$row['status'].",".str_replace($special_chr," ",$row['mediatorName'])."\r";
	}
	
	$path = $_SERVER['DOCUMENT_ROOT']."/tmp/";
	$filename = "PMC_Report-Pre-MediationStage_FROM_".$dateStart."to".$dateEnd.".csv";
	$fullPath = $path.$filename;
	$path_parts = pathinfo($fullPath);
	
	
	$fp = fopen($fullPath,'w') or die ("Error: can't generate report file.");
	fwrite($fp,$text);
	fclose($fp);
/*
	header("Cache-Control: no-store, no-cache, must-revalidate");  
	header("Cache-Control: post-check=0, pre-check=0", false);  
	header("Pragma: no-cache"); 
	header('Content-length: '.filesize($fullPath));
	header("Content-disposition: attachment; filename=\"".$path_parts["basename"]."\"");
	header("Content-type: application/vnd.ms-excel;charset:UTF-8");  
	readfile($fullPath);
*/
		
	// Delete last empty one
	//array_pop($array);
	//list($fn,$ext)= explode(".",$fullPath);
	//ConverToExcel($fullPath,$fn.".xls");
}	

function ExportCSVReportMediationPerf($dateStart,$dateEnd)
{

   include('connect-db.php');
   //--- setup file writing to tmp directory ---
   	$path = $_SERVER['DOCUMENT_ROOT']."/tmp/";
	$filename = "PMC_Report-MediatorPerf-FROM_".$dateStart."to".$dateEnd.".csv";
	$fullPath = $path.$filename;
	$path_parts = pathinfo($fullPath);
	
	
	$fp = fopen($fullPath,'w') or die ("Error: can't generate report file.");
	$text = "Name of Mediator, Referred Case,  Total Mediated, Successfully Mediated, Unsuccessfully Mediated, Ongoing, Success Rate\r";
	fwrite($fp,$text);
	
   //--- Load Mediator Ids ---
    //$sql_script = "SELECT mediator.mediatorId, mediator.mediatorName, schedule.status, casereferred.CaseNo FROM mediator LEFT JOIN casereferred ON mediator.mediatorId = casereferred.mediatorId LEFT JOIN schedule ON casereferred.CaseNo = schedule.caseNo ";
    $sql_script = "SELECT mediator.mediatorId FROM mediator ";	

    $result = mysql_query($sql_script)
				or die(mysql_error()); 
				
	$id=array();
	while($row = mysql_fetch_array($result)){
		array_push($id,$row['mediatorId']);
	}
//	print_r($id);
//	exit;
	
	//count total case
	foreach ($id as $v)
	{
		//-- get total referred case ---
		$sql_script = "SELECT mediator.mediatorName, schedule.status, casereferred.CaseNo, casereferred.caseStatus FROM mediator LEFT JOIN casereferred ON mediator.mediatorId = casereferred.mediatorId LEFT JOIN schedule ON casereferred.CaseNo = schedule.caseNo WHERE mediator.mediatorId = $v ";	
		$sql_script .= ' AND schedule.status != "reset" AND (casereferred.dateRegister BETWEEN "'.$dateStart.'" AND "'.$dateEnd.'")';

		$result = mysql_query($sql_script)
				or die(mysql_error()); 
		$totalcase = mysql_num_rows($result);

		if($totalcase)
		{
		// --- get mediator name --
			$myrow = mysql_fetch_array($result);
		//print_r($myrow);
		//die();
			$mediator = str_replace(","," ",$myrow['mediatorName']);
			$text = $mediator.",".$totalcase.",";
			$ongoing = 0;
			$success = 0;
			$unsuccess = 0;
			while ($myrow){
				if(strtolower($myrow['status']) == "ongoing")
					$ongoing++;
				if(strtolower($myrow['status']) == "settled")
					$success++;
				if (strtolower($myrow['status']) == "failed")
					$unsuccess++;
				$myrow = mysql_fetch_array($result);
			}
			$totalmediated = $unsuccess + $success;
			$perf = ($success / $totalmediated) * 100;
			//echo "DEBUG: success- $success totalmediated- $totalmediated";
			$text .= $totalmediated.",".$success.",".$unsuccess.",".$ongoing.",".$perf."% \r";
			fwrite($fp,$text);
		}
	}


	//--- close file --
	fclose($fp);
/*
	//automatic download
	header("Cache-Control: no-store, no-cache, must-revalidate");  
	header("Cache-Control: post-check=0, pre-check=0", false);  
	header('Content-length: '.filesize($fullPath));
	header("Pragma: no-cache"); 
	header("Content-disposition: attachment; filename=\"".$path_parts["basename"]."\"");
	readfile($fullPath);
*/

}

function ExportCSVReportJudgeReferral($dateStart,$dateEnd)
{

   include('connect-db.php');
   //--- setup file writing to tmp directory ---
   	$path = $_SERVER['DOCUMENT_ROOT']."/tmp/";
	$filename = "PMC_Report-JudgeReferral-FROM_".$dateStart."to".$dateEnd.".csv";
	$fullPath = $path.$filename;
	$path_parts = pathinfo($fullPath);
	
	
	$fp = fopen($fullPath,'w') or die ("Error: can't generate report file.");
	$text = "Name of Judge, Branch/Station, No. of  Case Referral to Mediation, Total Mediated, No. of Successful Mediation, No. of Unsuccessful Mediation,Ongoing, Success Rate\r";
	fwrite($fp,$text);
	
   //--- Load judges Ids ---
    //$sql_script = "SELECT mediator.mediatorId, mediator.mediatorName, schedule.status, casereferred.CaseNo FROM mediator LEFT JOIN casereferred ON mediator.mediatorId = casereferred.mediatorId LEFT JOIN schedule ON casereferred.CaseNo = schedule.caseNo ";
    $sql_script = "SELECT judgeId FROM judges ";	

    $result = mysql_query($sql_script)
				or die(mysql_error()); 
				
	$id=array();
	while($row = mysql_fetch_array($result)){
		array_push($id,$row['judgeId']);
	}
//	print_r($id);
//	exit;
	
	//count total case
	foreach ($id as $v)
	{
		//-- get total referred case ---
		$sql_script = "SELECT judges.judgeId,judges.referringJudge,judges.courtOrigin, schedule.status, casereferred.CaseNo, casereferred.caseStatus FROM judges LEFT JOIN casereferred ON judges.judgeId = casereferred.judgeId LEFT JOIN schedule ON casereferred.CaseNo = schedule.caseNo WHERE judges.judgeId = $v ";
		$sql_script .= ' AND (casereferred.dateRegister BETWEEN "'.$dateStart.'" AND "'.$dateEnd.'")';
		$sql_script .= ' AND schedule.status != "reset"';
		$result = mysql_query($sql_script)
				or die(mysql_error()); 
		$totalcase = mysql_num_rows($result);
		if($totalcase)
		{
		// --- get mediator name --
			$myrow = mysql_fetch_array($result);
		//print_r($myrow);
		//die();
			$refjudgename = str_replace(","," ",$myrow['referringJudge']);
			$branch = str_replace(","," ",$myrow['courtOrigin']);
			
			$ongoing = 0;
			$success = 0;
			$unsuccess = 0;
			while ($myrow){
				if(strtolower($myrow['status']) == "ongoing")
					$ongoing++;
				if(strtolower($myrow['status']) == "failed")
					$unsuccess++;
				if($myrow['caseStatus'] == "No")
					$success++;
				$myrow = mysql_fetch_array($result);
			}
			$totalmediated = $success+ $unsuccess;
			//$totalmediated = $ongoing + $success;
			if($totalmediated>0){
					$perf = ($success / $totalmediated) *100;
			}
			else
				$perf = 0;
			$text = $refjudgename.",".$branch.",".$totalcase.",".$totalmediated.",";
			$text .= $success.",".$unsuccess.",".$ongoing.",".$perf."% \r";
			fwrite($fp,$text);
		}
	}


	//--- close file --
	fclose($fp);
/*
	//automatic download
	header("Cache-Control: no-store, no-cache, must-revalidate");  
	header("Cache-Control: post-check=0, pre-check=0", false);  
	header('Content-length: '.filesize($fullPath));
	header("Pragma: no-cache"); 
	header("Content-disposition: attachment; filename=\"".$path_parts["basename"]."\"");
	readfile($fullPath);
*/

}

function ConverToExcel($src,$tgt)
{
$objReader = PHPExcel_IOFactory::createReader('CSV');
$objPHPExcel = $objReader->load($src);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save($tgt);
}
//delete all files in tmp directory
$path = $_SERVER['DOCUMENT_ROOT']."/tmp/";



//delete all files in tmp directory first
DeleteAllFiles($path);

$dS = $_GET['ds'];
$dE = $_GET['de'];
ExportCsvPMCPreMediation($dS,$dE);
ExportCSVReportMediationPerf($dS,$dE);
ExportCSVReportJudgeReferral($dS,$dE);


 
//get all image files with a .jpg extension.
$csv = array();
foreach (glob($path . "*.csv") as $filename)
{
	array_push($csv,basename($filename));
	//convert to excel
	//list($fn,$ext)=explode(".",$filename);
	//ConverToExcel($filename,$fn.".xls");
    //echo $fn."    \n\r";
}

//exit;
$archivename = $path."PMC_REPORT-".date("Y-m-d_His").".zip";

// zipFilesAndDownload($file_names,$archive_file_name,$file_path)
zipFilesAndDownload($csv,$archivename,$path);

 //print_r($csv);

?>
